﻿using ChurchDiary.Common;
using ChurchDiary.DAL.Contract;
using ChurchDiary.DAL.DAL;
using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace ChurchDiary.DAL.Methods
{
    public class DAL_ChurchMember : DataAccess, IMembers
    {
        #region ChurchMember Application
        public string InsertChurchMemberApplication(EntityChurchMemberApplications Applications)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberApplication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationId", DbType.Guid, Applications.ApplicationId);
                    SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, Applications.NameTitleId);
                    SQLServer.AddInParameter(dbCommand, "@Initial", DbType.String, Applications.Initial);
                    SQLServer.AddInParameter(dbCommand, "@ApplicantFirstName", DbType.String, Applications.ApplicantFirstName);
                    SQLServer.AddInParameter(dbCommand, "@ApplicantMiddleName", DbType.String, Applications.ApplicantMiddleName);
                    SQLServer.AddInParameter(dbCommand, "@ApplicantLastName", DbType.String, Applications.ApplicantLastName);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfChurch", DbType.String, Applications.PlaceOfChurch);
                    SQLServer.AddInParameter(dbCommand, "@SinceWhen", DbType.String, Applications.SinceWhen);
                    SQLServer.AddInParameter(dbCommand, "@ReasonforSelecting", DbType.String, Applications.ReasonforSelecting);
                    SQLServer.AddInParameter(dbCommand, "@PlaceAndChurch", DbType.String, Applications.PlaceAndChurch);
                    SQLServer.AddInParameter(dbCommand, "@PreviousChurch", DbType.String, Applications.PreviousChurch);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationDate", DbType.String, Applications.ApplicationDate);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationPlace", DbType.String, Applications.ApplicationPlace);
                    SQLServer.AddInParameter(dbCommand, "@ApplicantPhoto", DbType.String, CommonFunction.UploadImage(IMAGEPATH.APPLICANTPHOTO, Applications.ApplicantPhoto));
                    SQLServer.AddInParameter(dbCommand, "@ApplicantSoftCopy", DbType.String, CommonFunction.UploadImage(IMAGEPATH.APLICATIONSOFTCOPYPATH, Applications.ApplicantSoftCopy));
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@CurrentStatusId", DbType.Guid, Applications.Family.CurrentStatusId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, Applications.Family.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyName", DbType.String, Applications.Family.FamilyName);
                    SQLServer.AddInParameter(dbCommand, "@ContactNo", DbType.String, Applications.Family.ContactNo);
                    SQLServer.AddInParameter(dbCommand, "@ContactEmail", DbType.String, Applications.Family.ContactEmail);

                    SQLServer.AddInParameter(dbCommand, "@Occupation", DbType.String, Applications.Family.Occupation);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Applications.Family.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@GovermentProof", DbType.String, Applications.Family.GovermentProof);

                    SQLServer.AddInParameter(dbCommand, "@PresentCountry", DbType.Guid, Applications.Family.PresentAddress.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@PresentState", DbType.Guid, Applications.Family.PresentAddress.StateId);
                    SQLServer.AddInParameter(dbCommand, "@PresentCity", DbType.String, Applications.Family.PresentAddress.PresentCity);
                    SQLServer.AddInParameter(dbCommand, "@PresentAddress", DbType.String, Applications.Family.PresentAddress.PresentAddress);
                    SQLServer.AddInParameter(dbCommand, "@PresentPincode", DbType.String, Applications.Family.PresentAddress.PresentPinCode);
                    SQLServer.AddInParameter(dbCommand, "@IsSameAddress", DbType.Boolean, Applications.Family.PresentAddress.IsSameAddress);

                    SQLServer.AddInParameter(dbCommand, "@PermanentCountry", DbType.Guid, Applications.Family.PermanentAddress.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@PermanentState", DbType.Guid, Applications.Family.PermanentAddress.StateId);
                    SQLServer.AddInParameter(dbCommand, "@PermanentCity", DbType.String, Applications.Family.PermanentAddress.PermanentCity);
                    SQLServer.AddInParameter(dbCommand, "@PermanentAddress", DbType.String, Applications.Family.PermanentAddress.PermanentAddress);
                    SQLServer.AddInParameter(dbCommand, "@PermanentPincode", DbType.String, Applications.Family.PermanentAddress.PermanentPincode);

                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, Applications.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int16, 1);


                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                return IResult;
            }
            return IResult;
        }
        public string UpdateChurchMemberApplication(EntityChurchMemberApplications Applications)
        {
            string IResult = "SME";
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberApplication]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                SQLServer.AddInParameter(dbCommand, "@ApplicationId", DbType.Guid, Applications.ApplicationId);
                SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, Applications.NameTitleId);
                SQLServer.AddInParameter(dbCommand, "@Initial", DbType.String, Applications.Initial);
                SQLServer.AddInParameter(dbCommand, "@ApplicantFirstName", DbType.String, Applications.ApplicantFirstName);
                SQLServer.AddInParameter(dbCommand, "@ApplicantMiddleName", DbType.String, Applications.ApplicantMiddleName);
                SQLServer.AddInParameter(dbCommand, "@ApplicantLastName", DbType.String, Applications.ApplicantLastName);
                SQLServer.AddInParameter(dbCommand, "@PlaceOfChurch", DbType.String, Applications.PlaceOfChurch);
                SQLServer.AddInParameter(dbCommand, "@SinceWhen", DbType.String, Applications.SinceWhen);
                SQLServer.AddInParameter(dbCommand, "@ReasonforSelecting", DbType.String, Applications.ReasonforSelecting);
                SQLServer.AddInParameter(dbCommand, "@PlaceAndChurch", DbType.String, Applications.PlaceAndChurch);
                SQLServer.AddInParameter(dbCommand, "@PreviousChurch", DbType.String, Applications.PreviousChurch);
                SQLServer.AddInParameter(dbCommand, "@ApplicationDate", DbType.String, Applications.ApplicationDate);
                SQLServer.AddInParameter(dbCommand, "@ApplicationPlace", DbType.String, Applications.ApplicationPlace);
                SQLServer.AddInParameter(dbCommand, "@ApplicantPhoto", DbType.String, CommonFunction.UploadImage(IMAGEPATH.APPLICANTPHOTO, Applications.ApplicantPhoto));
                SQLServer.AddInParameter(dbCommand, "@ApplicantSoftCopy", DbType.String, CommonFunction.UploadImage(IMAGEPATH.APLICATIONSOFTCOPYPATH, Applications.ApplicantSoftCopy));
                SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                SQLServer.AddInParameter(dbCommand, "@CurrentStatusId", DbType.Guid, Applications.Family.CurrentStatusId);
                SQLServer.AddInParameter(dbCommand, "@FamilyName", DbType.String, Applications.Family.FamilyName);
                SQLServer.AddInParameter(dbCommand, "@ContactNo", DbType.String, Applications.Family.ContactNo);
                SQLServer.AddInParameter(dbCommand, "@ContactEmail", DbType.String, Applications.Family.ContactEmail);

                SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, Applications.Family.FamilyId);
                SQLServer.AddInParameter(dbCommand, "@Occupation", DbType.String, Applications.Family.Occupation);
                SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Applications.Family.Remarks);
                SQLServer.AddInParameter(dbCommand, "@GovermentProof", DbType.String, Applications.Family.GovermentProof);

                SQLServer.AddInParameter(dbCommand, "@PresentCountry", DbType.Guid, Applications.Family.PresentAddress.CountryId);
                SQLServer.AddInParameter(dbCommand, "@PresentState", DbType.Guid, Applications.Family.PresentAddress.StateId);
                SQLServer.AddInParameter(dbCommand, "@PresentCity", DbType.String, Applications.Family.PresentAddress.PresentCity);
                SQLServer.AddInParameter(dbCommand, "@PresentAddress", DbType.String, Applications.Family.PresentAddress.PresentAddress);
                SQLServer.AddInParameter(dbCommand, "@PresentPincode", DbType.String, Applications.Family.PresentAddress.PresentPinCode);
                SQLServer.AddInParameter(dbCommand, "@IsSameAddress", DbType.Boolean, Applications.Family.PresentAddress.IsSameAddress);

                SQLServer.AddInParameter(dbCommand, "@PermanentCountry", DbType.Guid, Applications.Family.PermanentAddress.CountryId);
                SQLServer.AddInParameter(dbCommand, "@PermanentState", DbType.Guid, Applications.Family.PermanentAddress.StateId);
                SQLServer.AddInParameter(dbCommand, "@PermanentCity", DbType.String, Applications.Family.PermanentAddress.PermanentCity);
                SQLServer.AddInParameter(dbCommand, "@PermanentAddress", DbType.String, Applications.Family.PermanentAddress.PermanentAddress);
                SQLServer.AddInParameter(dbCommand, "@PermanentPincode", DbType.String, Applications.Family.PermanentAddress.PermanentPincode);

                SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, Applications.IpAddress);
                SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int16, 1);

                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                }
            }
            return IResult;
        }
        public string DeleteChurchMemberApplication(Guid ApplicationId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberApplication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationId", DbType.Guid, ApplicationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchMemberApplications> GetChurchMemberApplication(int PageNo, string SearchKey, string Sort, string SortMode,int PerPage)
        {
            List<EntityChurchMemberApplications> Applications = new List<EntityChurchMemberApplications>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberApplication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);

                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Applications;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMemberApplications Application = new EntityChurchMemberApplications();
                                //APPLICANT DETAILS
                                Application.ApplicationId = (Guid)OReader["ApplicationId"];
                                Application.NameTitleId = (Guid)OReader["NameTitleId"];
                                Application.Initial = OReader["Initial"].ToString();
                                Application.ApplicantFirstName = OReader["ApplicantFirstName"].ToString();
                                Application.ApplicantMiddleName = OReader["ApplicantMiddleName"].ToString();
                                Application.ApplicantLastName = OReader["ApplicantLastName"].ToString();
                                Application.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Application.SinceWhen = OReader["SinceWhen"].ToString();
                                Application.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Application.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Application.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Application.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Application.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Application.ApplicantPhoto = CommonFunction.GetOrginalImagePath(OReader["ApplicantPhoto"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                Application.ApplicantSoftCopy = CommonFunction.GetOrginalImagePath(OReader["ApplicantSoftCopy"].ToString(), IMAGEPATH.APLICATIONSOFTCOPYPATH);
                                Application.ApplicationNo = (Int32)OReader["ApplicationNo"];
                                Application.TotalRecords = (Int32)OReader["TotalRecords"];
                                //FAMILY DETAILS
                                Application.Family = new EntityChurchMemberFamily();
                                Application.Family.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.ApplicationId = (Guid)OReader["ApplicationId"];
                                Application.Family.FamilyNo = (Int32)OReader["FamilyNo"];
                                Application.Family.Churchid = (Guid)OReader["Churchid"];
                                Application.Family.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                                Application.Family.FamilyName = OReader["FamilyName"].ToString();
                                Application.Family.ContactNo = OReader["ContactNo"].ToString();
                                Application.Family.ContactEmail = OReader["ContactEmail"].ToString();
                                Application.Family.Remarks = OReader["Remarks"].ToString();
                                Application.Family.GovermentProof = OReader["GovermentProof"].ToString();
                                Application.Family.Occupation = OReader["Occupation"].ToString();
                                //FAMILY PRRESENT DETAILS
                                Application.Family.PresentAddress = new EntityChurchFamilyPresentAddress();
                                Application.Family.PresentAddress.PresentAddressId = (Guid)OReader["PresentAddressId"];
                                Application.Family.PresentAddress.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.PresentAddress.CountryId = (Guid)OReader["PresentCountryId"];
                                Application.Family.PresentAddress.StateId = (Guid)OReader["PresentStateId"];
                                Application.Family.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                Application.Family.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                Application.Family.PresentAddress.PresentPinCode = OReader["PresentPinCode"].ToString();
                                Application.Family.PresentAddress.IsSameAddress = (bool)OReader["IsSameAddress"];
                                //FAMILY PRRESENT DETAILS
                                Application.Family.PermanentAddress = new EntityChurchFamilyPermanentAddress();
                                Application.Family.PermanentAddress.PermanentAddressId = (Guid)OReader["PermanentAddressId"];
                                Application.Family.PermanentAddress.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.PermanentAddress.CountryId = (Guid)OReader["PermanentCountryId"];
                                Application.Family.PermanentAddress.StateId = (Guid)OReader["PermanentStateId"];
                                Application.Family.PermanentAddress.PermanentCity = OReader["PermanentCity"].ToString();
                                Application.Family.PermanentAddress.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Application.Family.PermanentAddress.PermanentPincode = OReader["PermanentPincode"].ToString();
                                Applications.Add(Application);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Applications;
        }
        public EntityChurchMemberApplications GetChurchMemberApplication(Guid ApplicationId)
        {
            EntityChurchMemberApplications Application = new EntityChurchMemberApplications();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberApplication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationId", DbType.Guid, ApplicationId);
                    
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Application;
                            }
                            while (OReader.Read())
                            {
                                //APPLICANT DETAILS
                                Application.ApplicationId = (Guid)OReader["ApplicationId"];
                                Application.NameTitleId = (Guid)OReader["NameTitleId"];
                                Application.Initial = OReader["Initial"].ToString();
                                Application.ApplicantFirstName = OReader["ApplicantFirstName"].ToString();
                                Application.ApplicantMiddleName = OReader["ApplicantMiddleName"].ToString();
                                Application.ApplicantLastName = OReader["ApplicantLastName"].ToString();
                                Application.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Application.SinceWhen = OReader["SinceWhen"].ToString();
                                Application.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Application.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Application.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Application.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Application.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Application.ApplicantPhoto = CommonFunction.GetOrginalImagePath(OReader["ApplicantPhoto"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                Application.ApplicantSoftCopy = CommonFunction.GetOrginalImagePath(OReader["ApplicantSoftCopy"].ToString(), IMAGEPATH.APLICATIONSOFTCOPYPATH);
                                Application.ApplicationNo = (Int32)OReader["ApplicationNo"];
                                Application.NameTitle = new DAL_ChurchWorker().GetNameTitle(Application.NameTitleId);
                                
                                //FAMILY DETAILS

                                Application.Family = new EntityChurchMemberFamily();
                                Application.Family.ApplicationId = (Guid)OReader["ApplicationId"];
                                Application.Family.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.FamilyNo = (Int32)OReader["FamilyNo"];
                                Application.Family.Churchid = (Guid)OReader["Churchid"];
                                Application.Family.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                                Application.Family.FamilyName = OReader["FamilyName"].ToString();
                                Application.Family.ContactNo = OReader["ContactNo"].ToString();
                                Application.Family.ContactEmail = OReader["ContactEmail"].ToString();
                                Application.Family.Remarks = OReader["Remarks"].ToString();
                                Application.Family.GovermentProof = OReader["GovermentProof"].ToString();
                                Application.Family.Occupation = OReader["Occupation"].ToString();
                                //FAMILY PRRESENT DETAILS
                                Application.Family.PresentAddress = new EntityChurchFamilyPresentAddress();
                                Application.Family.PresentAddress.PresentAddressId = (Guid)OReader["PresentAddressId"];
                                Application.Family.PresentAddress.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.PresentAddress.CountryId = (Guid)OReader["PresentCountryId"];
                                Application.Family.PresentAddress.StateId = (Guid)OReader["PresentStateId"];
                                Application.Family.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                Application.Family.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                Application.Family.PresentAddress.PresentPinCode = OReader["PresentPinCode"].ToString();
                                Application.Family.PresentAddress.IsSameAddress = (bool)OReader["IsSameAddress"];
                                //FAMILY PRRESENT DETAILS
                                Application.Family.PermanentAddress = new EntityChurchFamilyPermanentAddress();
                                Application.Family.PermanentAddress.PermanentAddressId = (Guid)OReader["PermanentAddressId"];
                                Application.Family.PermanentAddress.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.PermanentAddress.CountryId = (Guid)OReader["PermanentCountryId"];
                                Application.Family.PermanentAddress.StateId = (Guid)OReader["PermanentStateId"];
                                Application.Family.PermanentAddress.PermanentCity = OReader["PermanentCity"].ToString();
                                Application.Family.PermanentAddress.PermanentAddress= OReader["PermanentAddress"].ToString();
                                Application.Family.PermanentAddress.PermanentPincode = OReader["PermanentPincode"].ToString();

                                
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Application;
        }
        #endregion
        #region ChurchMember Family
        public List<EntityChurchMemberFamily> GetChurchMemberFamilies(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityChurchMemberFamily> ChurchMemberFamilies = new List<EntityChurchMemberFamily>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberFamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchMemberFamilies;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMemberFamily ChurchWorkerFamily = new EntityChurchMemberFamily();
                                ChurchWorkerFamily.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchWorkerFamily.ApplicationId = (Guid)OReader["ApplicationId"];
                                ChurchWorkerFamily.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                                ChurchWorkerFamily.Churchid = (Guid)OReader["ChurchId"];
                                ChurchWorkerFamily.FamilyNo = (Int32)OReader["FamilyNo"];
                                ChurchWorkerFamily.FamilyName = OReader["FamilyName"].ToString();
                                ChurchWorkerFamily.ContactNo = OReader["ContactNo"].ToString();
                                ChurchWorkerFamily.ContactEmail = OReader["ContactEmail"].ToString();
                                ChurchWorkerFamily.Remarks = OReader["Remarks"].ToString();
                                ChurchWorkerFamily.GovermentProof = OReader["GovermentProof"].ToString();
                                ChurchWorkerFamily.TotalRecords = (Int32)OReader["TotalRecords"];
                                ChurchMemberFamilies.Add(ChurchWorkerFamily);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchMemberFamilies;
        }
        public EntityChurchMemberFamily GetChurchMemberFamily(Guid FamilyId)
        {
            EntityChurchMemberFamily ChurchMemberFamily = new EntityChurchMemberFamily();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberFamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchMemberFamily;
                            }
                            while (OReader.Read())
                            {
                                ChurchMemberFamily.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchMemberFamily.ApplicationId = (Guid)OReader["ApplicationId"];
                                ChurchMemberFamily.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                                ChurchMemberFamily.Churchid = (Guid)OReader["ChurchId"];
                                ChurchMemberFamily.FamilyNo = (Int32)OReader["FamilyNo"];
                                ChurchMemberFamily.FamilyName = OReader["FamilyName"].ToString();
                                ChurchMemberFamily.ContactNo = OReader["ContactNo"].ToString();
                                ChurchMemberFamily.ContactEmail = OReader["ContactEmail"].ToString();
                                ChurchMemberFamily.Remarks = OReader["Remarks"].ToString();
                                ChurchMemberFamily.GovermentProof = OReader["GovermentProof"].ToString();

                                ChurchMemberFamily.Applications = GetChurchMemberApplication(ChurchMemberFamily.ApplicationId);
                                ChurchMemberFamily.CurrentStatus = new DAL_ChurchWorker().GetChurchWorkerStatus(ChurchMemberFamily.CurrentStatusId);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchMemberFamily;
        }
        public string DeleteChurchWorkerFamily(Guid FamilyId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberFamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion ChurchMember Family
        #region ChurchMember
        public string TransOldFamilytoApplicantTable()
        {
            List<EntityChurchMembers> ChurchMembers = new List<EntityChurchMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[Old_GET_Churchfamily]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return "No Data Found";
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMemberApplications Application = new EntityChurchMemberApplications();
                                Application.ApplicationId = Guid.NewGuid();
                                Application.NameTitleId = (Guid)OReader["NameTitleId"];
                                Application.Initial = "";
                                Application.ApplicantFirstName = OReader["ApplicantName"].ToString();
                                Application.ApplicantMiddleName = "";
                                Application.ApplicantLastName = "";
                                Application.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Application.SinceWhen = OReader["SinceWhen"].ToString();
                                Application.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Application.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Application.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Application.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Application.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Application.ApplicantPhoto = OReader["Photo"].ToString();
                                Application.ApplicantSoftCopy = OReader["SoftCopy"].ToString();
                                Application.CreatedBy = (Guid)OReader["CreatedBy"];
                                Application.CreatedOn = DateTime.Now;
                                Application.UpdatedBy = (Guid)OReader["CreatedBy"];
                                Application.UpdatedOn = DateTime.Now;
                                Application.IpAddress = "";
                                Application.Family = new EntityChurchMemberFamily();
                                Application.Family.FamilyId = (Guid)OReader["FamilyId"];
                                Application.Family.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                                Application.Family.FamilyName = OReader["FamilyName"].ToString();
                                Application.Family.ContactNo = OReader["TelephoneNo"].ToString();
                                Application.Family.ContactEmail = OReader["Email"].ToString();
                                Application.Family.Occupation = OReader["Occupation"].ToString();
                                Application.Family.Remarks = "";
                                Application.Family.GovermentProof = "";

                                Application.Family.PresentAddress = new EntityChurchFamilyPresentAddress();
                                Application.Family.PresentAddress.CountryId = (Guid)OReader["PresentCountry"];
                                Application.Family.PresentAddress.StateId = (Guid)OReader["PresentState"];
                                Application.Family.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                Application.Family.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                Application.Family.PresentAddress.PresentPinCode = OReader["PresentPinCode"].ToString();
                                Application.Family.PresentAddress.IsSameAddress = (bool)OReader["IsSameAddress"];

                                Application.Family.PermanentAddress = new EntityChurchFamilyPermanentAddress();
                                Application.Family.PermanentAddress.CountryId = (Guid)OReader["PermanentCountry"];
                                Application.Family.PermanentAddress.StateId = (Guid)OReader["PermanentState"];
                                Application.Family.PermanentAddress.PermanentCity = OReader["PermanentCity"].ToString();
                                Application.Family.PermanentAddress.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Application.Family.PermanentAddress.PermanentPincode = OReader["PermanentPincode"].ToString();

                                InsertChurchMemberApplication(Application);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return "";
        }
        public string TransOldFamilytoChurchMembers()
        {
            List<EntityChurchMembers> ChurchMembers = new List<EntityChurchMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[Old_GET_FamilyMembers]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return "No Data Found";
                            }
                            while (OReader.Read())
                            {
                                bool Checkstring = true;
                                EntityChurchMembers ChurchMember = new EntityChurchMembers();
                                ChurchMember.CreatedBy = Current.Userid;
                                ChurchMember.CreatedOn = DateTime.Now;
                                ChurchMember.CurrentAge = 0;
                                ChurchMember.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchMember.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchMember.DateOfDedication = OReader["DateOfDedication"].ToString();
                                ChurchMember.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                ChurchMember.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                ChurchMember.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchMember.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchMember.Email = OReader["Email"].ToString();
                                ChurchMember.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchMember.FirstName = OReader["Name"].ToString();
                                ChurchMember.FullName = OReader["Name"].ToString();
                                ChurchMember.Gender = (int)OReader["Gender"];
                                ChurchMember.GovermentProof = "";
                                ChurchMember.IdentityNumber = "";
                                ChurchMember.IpAddress = "";
                                ChurchMember.IsApplicant = false;
                                ChurchMember.IsGotBaptized = OReader["DateOfBaptism"].ToString() != null ? Checkstring : false;
                                ChurchMember.IsGotHgBaptism = OReader["DateOfHgBaptism"].ToString() != null ? Checkstring : false;  
                                ChurchMember.IsGotMarried = OReader["DateOfMarriage"].ToString() != null ? Checkstring : false;  
                                ChurchMember.IsGotSalvation = OReader["DateOfSalvation"].ToString() != null ? Checkstring : false;
                                ChurchMember.LastName = "";
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MiddleName = "";
                                ChurchMember.NameTitleId = (Guid)OReader["NameTitleId"];
                                ChurchMember.Occupation = OReader["Occupation"].ToString();
                                ChurchMember.Phone = OReader["Phone"].ToString();
                                ChurchMember.Photo = "";
                                ChurchMember.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                ChurchMember.RelationShipId = (Guid)OReader["RelationShipId"];
                                ChurchMember.Remarks = OReader["Remarks"].ToString();
                                ChurchMember.StatusId = (Guid)OReader["StatusId"];
                                ChurchMember.UpdatedOn = DateTime.Now;
                                ChurchMember.UpdatedBy = null;

                                InsertChurchMembers(ChurchMember);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return "";
        }
        public List<EntityChurchMembers> GetChurchMembers(Guid FamilyId)
        {
            List<EntityChurchMembers> ChurchMembers = new List<EntityChurchMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "CHURCHMEMBERBYFAMILY");
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchMembers;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMembers ChurchMember = new EntityChurchMembers();
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchMember.StatusId = (Guid)OReader["StatusId"];
                                ChurchMember.NameTitleId = (Guid)OReader["NameTitleId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberNumber = (Int32)OReader["MemberNumber"];
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.FirstName = OReader["FirstName"].ToString();
                                ChurchMember.MiddleName = OReader["MiddleName"].ToString();
                                ChurchMember.LastName = OReader["LastName"].ToString();
                                ChurchMember.FullName = OReader["FullName"].ToString();
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.Gender = (Int32)OReader["Gender"];
                                ChurchMember.Phone = OReader["Phone"].ToString();
                                ChurchMember.Email = OReader["Email"].ToString();
                                ChurchMember.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchMember.DateOfDedication = OReader["DateOfDedication"].ToString();
                                ChurchMember.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchMember.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchMember.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                ChurchMember.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchMember.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                ChurchMember.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                ChurchMember.Remarks = OReader["Remarks"].ToString();
                                ChurchMember.IsGotBaptized = (bool)OReader["IsGotBaptized"];
                                ChurchMember.IsGotMarried = (bool)OReader["IsGotMarried"];
                                ChurchMember.IsGotHgBaptism = (bool)OReader["IsGotHgBaptism"];
                                ChurchMember.CurrentAge = (int)OReader["CurrentAge"];
                                ChurchMember.Photo = CommonFunction.GetOrginalImagePath(OReader["Photo"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                ChurchMember.Occupation = OReader["Occupation"].ToString();
                                ChurchMember.IdentityNumber = OReader["IdentityNumber"].ToString();
                                ChurchMember.GovermentProof = OReader["GovermentProof"].ToString();
                                ChurchMember.IsApplicant = (bool)OReader["IsApplicant"];

                                ChurchMember.NameTitle = new DAL_ChurchWorker().GetNameTitle(ChurchMember.NameTitleId);
                                ChurchMember.ChurchWorkerStatus = new DAL_ChurchWorker().GetChurchWorkerStatus(ChurchMember.StatusId);
                                if (!string.IsNullOrEmpty(OReader["RelationShipId"].ToString()))
                                {
                                    ChurchMember.RelationShipId = (Guid)OReader["RelationShipId"];
                                    ChurchMember.RelationShip = new DAL_Shared().GetRelationShipByID(ChurchMember.RelationShipId);
                                }
                                ChurchMember.Family = GetChurchMemberFamily(ChurchMember.FamilyId);

                                ChurchMembers.Add(ChurchMember);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchMembers;
        }
        public List<EntityChurchMembers> GetChurchMembers()
        {
            List<EntityChurchMembers> ChurchMembers = new List<EntityChurchMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ALLCHURCHMEMBERBYCHURCH");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchMembers;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMembers ChurchMember = new EntityChurchMembers();
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchMember.StatusId = (Guid)OReader["StatusId"];
                                ChurchMember.NameTitleId = (Guid)OReader["NameTitleId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberNumber = (Int32)OReader["MemberNumber"];
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.FirstName = OReader["FirstName"].ToString();
                                ChurchMember.MiddleName = OReader["MiddleName"].ToString();
                                ChurchMember.LastName = OReader["LastName"].ToString();
                                ChurchMember.FullName = OReader["FullName"].ToString();
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.Gender = (Int32)OReader["Gender"];
                                ChurchMember.Phone = OReader["Phone"].ToString();
                                ChurchMember.Email = OReader["Email"].ToString();
                                ChurchMember.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchMember.DateOfDedication = OReader["DateOfDedication"].ToString();
                                ChurchMember.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchMember.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchMember.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                ChurchMember.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchMember.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                ChurchMember.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                ChurchMember.Remarks = OReader["Remarks"].ToString();
                                ChurchMember.IsGotBaptized = (bool)OReader["IsGotBaptized"];
                                ChurchMember.IsGotMarried = (bool)OReader["IsGotMarried"];
                                ChurchMember.IsGotHgBaptism = (bool)OReader["IsGotHgBaptism"];
                                ChurchMember.CurrentAge = (int)OReader["CurrentAge"];
                                ChurchMember.Photo = CommonFunction.GetOrginalImagePath(OReader["Photo"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                ChurchMember.Occupation = OReader["Occupation"].ToString();
                                ChurchMember.IdentityNumber = OReader["IdentityNumber"].ToString();
                                ChurchMember.GovermentProof = OReader["GovermentProof"].ToString();
                                ChurchMember.IsApplicant = (bool)OReader["IsApplicant"];

                                //ChurchMember.NameTitle = new DAL_ChurchWorker().GetNameTitle(ChurchMember.NameTitleId);
                                //ChurchMember.ChurchWorkerStatus = new DAL_ChurchWorker().GetChurchWorkerStatus(ChurchMember.StatusId);
                                //if (!string.IsNullOrEmpty(OReader["RelationShipId"].ToString()))
                                //{
                                //    ChurchMember.RelationShipId = (Guid)OReader["RelationShipId"];
                                //    ChurchMember.RelationShip = new DAL_Shared().GetRelationShipByID(ChurchMember.RelationShipId);
                                //}
                                //ChurchMember.Family = GetChurchMemberFamily(ChurchMember.FamilyId);

                                ChurchMembers.Add(ChurchMember);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchMembers;
        }
        public EntityChurchMembers GetChurchMember(Guid MemberId)
        {
            EntityChurchMembers ChurchMember = new EntityChurchMembers();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, MemberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchMember;
                            }
                            while (OReader.Read())
                            {
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchMember.StatusId = (Guid)OReader["StatusId"];
                                ChurchMember.NameTitleId = (Guid)OReader["NameTitleId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberNumber = (Int32)OReader["MemberNumber"];
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.FirstName = OReader["FirstName"].ToString();
                                ChurchMember.MiddleName = OReader["MiddleName"].ToString();
                                ChurchMember.LastName = OReader["LastName"].ToString();
                                ChurchMember.FullName = OReader["FullName"].ToString();
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.Gender = (Int32)OReader["Gender"];
                                ChurchMember.Phone = OReader["Phone"].ToString();
                                ChurchMember.Email = OReader["Email"].ToString();
                                ChurchMember.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchMember.DateOfDedication = OReader["DateOfDedication"].ToString();
                                ChurchMember.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchMember.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchMember.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                ChurchMember.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchMember.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                ChurchMember.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                ChurchMember.Remarks = OReader["Remarks"].ToString();
                                ChurchMember.IsGotBaptized = (bool)OReader["IsGotBaptized"];
                                ChurchMember.IsGotMarried = (bool)OReader["IsGotMarried"];
                                ChurchMember.IsGotHgBaptism = (bool)OReader["IsGotHgBaptism"];
                                ChurchMember.IsGotSalvation = (bool)OReader["IsGotSalvation"];
                                
                                ChurchMember.CurrentAge = (int)OReader["CurrentAge"];
                                ChurchMember.Photo = CommonFunction.GetOrginalImagePath(OReader["Photo"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                ChurchMember.Occupation = OReader["Occupation"].ToString();
                                ChurchMember.IdentityNumber = OReader["IdentityNumber"].ToString();
                                ChurchMember.GovermentProof = OReader["GovermentProof"].ToString();
                                ChurchMember.IsApplicant = (bool)OReader["IsApplicant"];


                                ChurchMember.NameTitle = new DAL_ChurchWorker().GetNameTitle(ChurchMember.NameTitleId);
                                ChurchMember.ChurchWorkerStatus = new DAL_ChurchWorker().GetChurchWorkerStatus(ChurchMember.StatusId);
                                if (!string.IsNullOrEmpty(OReader["RelationShipId"].ToString()))
                                {
                                    ChurchMember.RelationShipId = (Guid)OReader["RelationShipId"];
                                    ChurchMember.RelationShip = new DAL_Shared().GetRelationShipByID(ChurchMember.RelationShipId);
                                }
                                ChurchMember.Family = GetChurchMemberFamily(ChurchMember.FamilyId);

                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchMember;
        }
        public List<EntityChurchMembers> GetChurchMembers(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityChurchMembers> ChurchMembers = new List<EntityChurchMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "CHURCHMEMBERBYCHURCH");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchMembers;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMembers ChurchMember = new EntityChurchMembers();
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.FamilyId = (Guid)OReader["FamilyId"];
                                ChurchMember.StatusId = (Guid)OReader["StatusId"];
                                ChurchMember.NameTitleId = (Guid)OReader["NameTitleId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberId = (Guid)OReader["MemberId"];
                                ChurchMember.MemberNumber = (Int32)OReader["MemberNumber"];
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.FirstName = OReader["FirstName"].ToString();
                                ChurchMember.MiddleName = OReader["MiddleName"].ToString();
                                ChurchMember.LastName = OReader["LastName"].ToString();
                                ChurchMember.FullName = OReader["FullName"].ToString();
                                ChurchMember.Initial = OReader["Initial"].ToString();
                                ChurchMember.Gender = (Int32)OReader["Gender"];
                                ChurchMember.Phone = OReader["Phone"].ToString();
                                ChurchMember.Email = OReader["Email"].ToString();
                                ChurchMember.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchMember.DateOfDedication = OReader["DateOfDedication"].ToString();
                                ChurchMember.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchMember.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchMember.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                ChurchMember.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchMember.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                ChurchMember.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                ChurchMember.Remarks = OReader["Remarks"].ToString();
                                ChurchMember.IsGotBaptized = (bool)OReader["IsGotBaptized"];
                                ChurchMember.IsGotMarried = (bool)OReader["IsGotMarried"];
                                ChurchMember.IsGotHgBaptism = (bool)OReader["IsGotHgBaptism"];
                                ChurchMember.CurrentAge = (int)OReader["CurrentAge"];
                                ChurchMember.Photo = CommonFunction.GetOrginalImagePath(OReader["Photo"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                ChurchMember.Occupation = OReader["Occupation"].ToString();
                                ChurchMember.IdentityNumber = OReader["IdentityNumber"].ToString();
                                ChurchMember.GovermentProof = OReader["GovermentProof"].ToString();
                                ChurchMember.IsApplicant = (bool)OReader["IsApplicant"];
                                ChurchMember.TotalRecords = (Int32)OReader["TotalRecords"];

                                ChurchMember.NameTitle = new DAL_ChurchWorker().GetNameTitle(ChurchMember.NameTitleId);
                                ChurchMember.ChurchWorkerStatus = new DAL_ChurchWorker().GetChurchWorkerStatus(ChurchMember.StatusId);
                                if (!string.IsNullOrEmpty(OReader["RelationShipId"].ToString()))
                                {
                                    ChurchMember.RelationShipId = (Guid)OReader["RelationShipId"];
                                    ChurchMember.RelationShip = new DAL_Shared().GetRelationShipByID(ChurchMember.RelationShipId);
                                }
                                ChurchMember.Family = GetChurchMemberFamily(ChurchMember.FamilyId);

                                ChurchMembers.Add(ChurchMember);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchMembers;
        }
        public string InsertChurchMembers(EntityChurchMembers Member)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, Member.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@StatusId", DbType.Guid, Member.StatusId);
                    SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, Member.NameTitleId);
                    SQLServer.AddInParameter(dbCommand, "@RelationShipId", DbType.Guid, Member.RelationShipId);
                    SQLServer.AddInParameter(dbCommand, "@Initial", DbType.String, Member.Initial);
                    SQLServer.AddInParameter(dbCommand, "@FirstName", DbType.String, Member.FirstName);
                    SQLServer.AddInParameter(dbCommand, "@MiddleName", DbType.String, Member.MiddleName);
                    SQLServer.AddInParameter(dbCommand, "@LastName", DbType.String, Member.LastName);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, Member.Gender);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, Member.Phone);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Member.Email);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, Member.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@DateOfDedication", DbType.String, Member.DateOfDedication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfSalvation", DbType.String, Member.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, Member.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfHgBaptism", DbType.String, Member.DateOfHgBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfMarriage", DbType.String, Member.DateOfMarriage);
                    SQLServer.AddInParameter(dbCommand, "@DateOfLeaving", DbType.String, Member.DateOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@ReasonOfLeaving", DbType.String, Member.ReasonOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Member.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@IsGotBaptized", DbType.Boolean, Member.IsGotBaptized);
                    SQLServer.AddInParameter(dbCommand, "@IsGotSalvation", DbType.Boolean, Member.IsGotSalvation);
                    SQLServer.AddInParameter(dbCommand, "@IsGotMarried", DbType.Boolean, Member.IsGotMarried);
                    SQLServer.AddInParameter(dbCommand, "@IsGotHgBaptism", DbType.Boolean, Member.IsGotHgBaptism);
                    SQLServer.AddInParameter(dbCommand, "@Photo", DbType.String, CommonFunction.UploadImage(IMAGEPATH.APPLICANTPHOTO, Member.Photo));
                    SQLServer.AddInParameter(dbCommand, "@Occupation", DbType.String, Member.Occupation);
                    SQLServer.AddInParameter(dbCommand, "@IdentityNumber", DbType.String, Member.IdentityNumber);
                    SQLServer.AddInParameter(dbCommand, "@GovermentProof", DbType.String, Member.GovermentProof);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, Member.IpAddress);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchMembers(EntityChurchMembers Member)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Member.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, Member.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@StatusId", DbType.Guid, Member.StatusId);
                    SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, Member.NameTitleId);
                    SQLServer.AddInParameter(dbCommand, "@RelationShipId", DbType.Guid, Member.RelationShipId);
                    SQLServer.AddInParameter(dbCommand, "@Initial", DbType.String, Member.Initial);
                    SQLServer.AddInParameter(dbCommand, "@FirstName", DbType.String, Member.FirstName);
                    SQLServer.AddInParameter(dbCommand, "@MiddleName", DbType.String, Member.MiddleName);
                    SQLServer.AddInParameter(dbCommand, "@LastName", DbType.String, Member.LastName);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, Member.Gender);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, Member.Phone);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Member.Email);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, Member.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@DateOfDedication", DbType.String, Member.DateOfDedication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfSalvation", DbType.String, Member.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, Member.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfHgBaptism", DbType.String, Member.DateOfHgBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfMarriage", DbType.String, Member.DateOfMarriage);
                    SQLServer.AddInParameter(dbCommand, "@DateOfLeaving", DbType.String, Member.DateOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@ReasonOfLeaving", DbType.String, Member.ReasonOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Member.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@IsGotBaptized", DbType.Boolean, Member.IsGotBaptized);
                    SQLServer.AddInParameter(dbCommand, "@IsGotSalvation", DbType.Boolean, Member.IsGotSalvation);
                    SQLServer.AddInParameter(dbCommand, "@IsGotMarried", DbType.Boolean, Member.IsGotMarried);
                    SQLServer.AddInParameter(dbCommand, "@IsGotHgBaptism", DbType.Boolean, Member.IsGotHgBaptism);
                    SQLServer.AddInParameter(dbCommand, "@Photo", DbType.String, CommonFunction.UploadImage(IMAGEPATH.APPLICANTPHOTO, Member.Photo));
                    SQLServer.AddInParameter(dbCommand, "@Occupation", DbType.String, Member.Occupation);
                    SQLServer.AddInParameter(dbCommand, "@IdentityNumber", DbType.String, Member.IdentityNumber);
                    SQLServer.AddInParameter(dbCommand, "@GovermentProof", DbType.String, Member.GovermentProof);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, Member.IpAddress);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchMembers(Guid MemberId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMember]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, MemberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchMembers> GetBirthDayList(Guid ChurchId)
        {
            List<EntityChurchMembers> BirthDays = new List<EntityChurchMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_BirthDays]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, BirthDay.TODAY);
                    SQLServer.AddInParameter(dbCommand, "@Usertype", DbType.String, "BELIVER");
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return BirthDays;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchMembers Birthday = new EntityChurchMembers();
                                Birthday.FullName = OReader["FullName"].ToString();
                                Birthday.Phone = OReader["Phone"].ToString();
                                Birthday.Email = OReader["Email"].ToString();
                                Birthday.CurrentAge = (Int32)OReader["CurrentAge"];
                                Birthday.DateOfBirth = OReader["DateOfBirth"].ToString();
                                Birthday.MemberId = (Guid)OReader["MemberId"];
                                Birthday.Photo = CommonFunction.GetOrginalImagePath(OReader["Photo"].ToString(), IMAGEPATH.APPLICANTPHOTO);
                                Birthday.Family = new EntityChurchMemberFamily();
                                Birthday.Family.FamilyName = OReader["FamilyName"].ToString();
                                BirthDays.Add(Birthday);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return BirthDays;
        }
        #endregion
        #region ChurchMember Jobtitle
        public string InsertChurchMemberJobtitle(EntityChurchMemberJobtitle Jobtitle)
        {
            string IResult = "SME";
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberJobtitle]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                SQLServer.AddInParameter(dbCommand, "@ChurchBoardId", DbType.Guid, Jobtitle.ChurchBoardId);
                SQLServer.AddInParameter(dbCommand, "@ChurchBoardJobtitleId", DbType.Guid, Jobtitle.ChurchBoardJobtitleId);
                SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, Jobtitle.CreateLogin);
                SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Jobtitle.Email);
                SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Jobtitle.MemberId);
                SQLServer.AddInParameter(dbCommand, "@CurrentStatusId", DbType.Guid, Jobtitle.CurrentStatusId);
                SQLServer.AddInParameter(dbCommand, "@EffectiveYear", DbType.Int32, Jobtitle.EffectiveYear);
                SQLServer.AddInParameter(dbCommand, "@EffectiveFrom", DbType.String, Jobtitle.EffectiveFrom);
                SQLServer.AddInParameter(dbCommand, "@EffectiveTo", DbType.String, Jobtitle.EffectiveTo);
                SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Jobtitle.Remarks);
                SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, Jobtitle.IpAddress);
                SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                }
            }
            return IResult;
        }
        public string UpdateChurchMemberJobtitle(EntityChurchMemberJobtitle Jobtitle)
        {
            string IResult = "SME";
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberJobtitle]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                SQLServer.AddInParameter(dbCommand, "@ChurchMemberJobtitleId", DbType.Guid, Jobtitle.ChurchMemberJobtitleId);
                SQLServer.AddInParameter(dbCommand, "@ChurchBoardId", DbType.Guid, Jobtitle.ChurchBoardId);
                SQLServer.AddInParameter(dbCommand, "@ChurchBoardJobtitleId", DbType.Guid, Jobtitle.ChurchBoardJobtitleId);
                SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, Jobtitle.CreateLogin);
                SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Jobtitle.Email);
                SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Jobtitle.MemberId);
                SQLServer.AddInParameter(dbCommand, "@CurrentStatusId", DbType.Guid, Jobtitle.CurrentStatusId);
                SQLServer.AddInParameter(dbCommand, "@EffectiveYear", DbType.Int32, Jobtitle.EffectiveYear);
                SQLServer.AddInParameter(dbCommand, "@EffectiveFrom", DbType.String, Jobtitle.EffectiveFrom);
                SQLServer.AddInParameter(dbCommand, "@EffectiveTo", DbType.String, Jobtitle.EffectiveTo);
                SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Jobtitle.Remarks);
                SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, Jobtitle.IpAddress);
                SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                }
            }
            return IResult;
        }
        public string DeleteChurchMemberJobtitle(Guid JobtitleId)
        {
            string IResult = "SME";
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchMemberJobtitle]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                SQLServer.AddInParameter(dbCommand, "@ChurchMemberJobtitleId", DbType.Guid, JobtitleId);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                }
            }
            return IResult;
        }
        public EntityChurchMemberJobtitle GetChurchMemberJobtitle(Guid JobtitleId)
        {
            EntityChurchMemberJobtitle Jobtitle = new EntityChurchMemberJobtitle();
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberJobtitle]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                SQLServer.AddInParameter(dbCommand, "@ChurchMemberJobtitleId", DbType.Guid, JobtitleId);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                    {
                        if (OReader == null)
                        {
                            return Jobtitle;
                        }
                        while (OReader.Read())
                        {
                            Jobtitle.ChurchMemberJobtitleId = (Guid)OReader["ChurchMemberJobtitleId"];
                            Jobtitle.ChurchBoardId = (Guid)OReader["ChurchBoardId"];
                            Jobtitle.ChurchBoardJobtitleId = (Guid)OReader["ChurchBoardJobtitleId"];
                            Jobtitle.ChurchId = (Guid)OReader["ChurchId"];
                            Jobtitle.MemberId = (Guid)OReader["MemberId"];
                            Jobtitle.CreateLogin = (bool)OReader["CreateLogin"];
                            Jobtitle.Email = OReader["Email"].ToString();
                            Jobtitle.EffectiveYear = (Int32)OReader["EffectiveYear"];
                            Jobtitle.EffectiveFrom = OReader["EffectiveFrom"].ToString();
                            Jobtitle.EffectiveTo = OReader["EffectiveTo"].ToString();
                            Jobtitle.Remarks = OReader["Remarks"].ToString();
                            Jobtitle.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                            Jobtitle.Members = new EntityChurchMembers();
                            Jobtitle.Members.FirstName = OReader["FirstName"].ToString();
                            Jobtitle.Members.FullName = OReader["FullName"].ToString();
                            Jobtitle.CurrentStatus = new EntityChurchWorkerStatus();
                            Jobtitle.CurrentStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();
                            Jobtitle.BoardJobtitle = new EntityChurchBoardJobtitle();
                            Jobtitle.BoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                            Jobtitle.ChurchBoard = new EntityChurchBoard();
                            Jobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                        }
                    }
                }
            }
            return Jobtitle;
        }
        public  List<EntityChurchMemberJobtitle> GetChurchMemberJobtitle()
        {
            List<EntityChurchMemberJobtitle> Jobtitles = new List<EntityChurchMemberJobtitle>();
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberJobtitle]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                    {
                        if (OReader == null)
                        {
                            return Jobtitles;
                        }
                        while (OReader.Read())
                        {
                            EntityChurchMemberJobtitle Jobtitle = new EntityChurchMemberJobtitle();
                            Jobtitle.ChurchMemberJobtitleId = (Guid)OReader["ChurchMemberJobtitleId"];
                            Jobtitle.ChurchBoardId = (Guid)OReader["ChurchBoardId"];
                            Jobtitle.ChurchBoardJobtitleId = (Guid)OReader["ChurchBoardJobtitleId"];
                            Jobtitle.ChurchId = (Guid)OReader["ChurchId"];
                            Jobtitle.MemberId = (Guid)OReader["MemberId"];
                            Jobtitle.CreateLogin = (bool)OReader["CreateLogin"];
                            Jobtitle.Email = OReader["Email"].ToString();
                            Jobtitle.EffectiveYear = (Int32)OReader["EffectiveYear"];
                            Jobtitle.EffectiveFrom = OReader["EffectiveFrom"].ToString();
                            Jobtitle.EffectiveTo = OReader["EffectiveTo"].ToString();
                            Jobtitle.Remarks = OReader["Remarks"].ToString();
                            Jobtitle.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                            Jobtitle.Members = new EntityChurchMembers();
                            Jobtitle.Members.FirstName = OReader["FirstName"].ToString();
                            Jobtitle.Members.FullName = OReader["FullName"].ToString();
                            Jobtitle.CurrentStatus = new EntityChurchWorkerStatus();
                            Jobtitle.CurrentStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();
                            Jobtitle.BoardJobtitle = new EntityChurchBoardJobtitle();
                            Jobtitle.BoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                            Jobtitle.ChurchBoard = new EntityChurchBoard();
                            Jobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                            Jobtitles.Add(Jobtitle);
                        }
                    }
                }
            }
            return Jobtitles;
        }
        public List<EntityChurchMemberJobtitle> GetChurchMemberJobtitle(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityChurchMemberJobtitle> Jobtitles = new List<EntityChurchMemberJobtitle>();
            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchMemberJobtitle]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                    {
                        if (OReader == null)
                        {
                            return Jobtitles;
                        }
                        while (OReader.Read())
                        {
                            EntityChurchMemberJobtitle Jobtitle = new EntityChurchMemberJobtitle();
                            Jobtitle.ChurchMemberJobtitleId = (Guid)OReader["ChurchMemberJobtitleId"];
                            Jobtitle.ChurchBoardId = (Guid)OReader["ChurchBoardId"];
                            Jobtitle.ChurchBoardJobtitleId = (Guid)OReader["ChurchBoardJobtitleId"];
                            Jobtitle.ChurchId = (Guid)OReader["ChurchId"];
                            Jobtitle.MemberId = (Guid)OReader["MemberId"];
                            Jobtitle.CreateLogin = (bool)OReader["CreateLogin"];
                            Jobtitle.Email = OReader["Email"].ToString();
                            Jobtitle.EffectiveYear = (Int32)OReader["EffectiveYear"];
                            Jobtitle.EffectiveFrom = OReader["EffectiveFrom"].ToString();
                            Jobtitle.EffectiveTo = OReader["EffectiveTo"].ToString();
                            Jobtitle.Remarks = OReader["Remarks"].ToString();
                            Jobtitle.CurrentStatusId = (Guid)OReader["CurrentStatusId"];
                            Jobtitle.Members = new EntityChurchMembers();
                            Jobtitle.Members.FirstName = OReader["FirstName"].ToString();
                            Jobtitle.Members.FullName = OReader["FullName"].ToString();
                            Jobtitle.CurrentStatus = new EntityChurchWorkerStatus();
                            Jobtitle.CurrentStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();
                            Jobtitle.BoardJobtitle = new EntityChurchBoardJobtitle();
                            Jobtitle.BoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                            Jobtitle.ChurchBoard = new EntityChurchBoard();
                            Jobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                            Jobtitles.Add(Jobtitle);
                        }
                    }
                }
            }
            return Jobtitles;
        }
        #endregion region ChurchMember Jobtitle
        /*#region Family
        /// <summary>
        ///</summary>
        /// <param name="ChurchId">Church Id</param>
        /// <returns>List of Families under the church and its family member</returns>
        public List<EntityMembers> GetFamiliesWithMembers(Guid ChurchId)
        {
            List<EntityMembers> Members = new List<EntityMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "FAMILYMEMBERS");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Members;
                            }
                            while (OReader.Read())
                            {
                                EntityMembers Member = new EntityMembers();
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.CHChurchid = (Guid)OReader["CHChurchid"];
                                Member.FamilyNo = (Int32)OReader["FamilyNo"];
                                Member.ApplicantName = OReader["ApplicantName"].ToString();
                                Member.FamilyName = OReader["FamilyName"].ToString();
                                Member.TelephoneNo = OReader["TelephoneNo"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Occupation = OReader["Occupation"].ToString();
                                Member.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Member.SinceWhen = OReader["SinceWhen"].ToString();
                                Member.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Member.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Member.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Member.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Member.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Member.SoftCopy = OReader["SoftCopy"].ToString();
                                Member.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Member.PermanentCity = OReader["PermanentCity"].ToString();
                                Member.PermanentCountry = (Guid)OReader["PermanentCountry"];
                                Member.PermanentState = (Guid)OReader["PermanentState"];
                                Member.PermanentPincode = OReader["PermanentPincode"].ToString();
                                Member.PresentAddress = OReader["PresentAddress"].ToString();
                                Member.PresentCity = OReader["PresentCity"].ToString();
                                Member.PresentCountry = (Guid)OReader["PresentCountry"];
                                Member.PresentState = (Guid)OReader["PresentState"];
                                Member.PresentPinCode = OReader["PresentPinCode"].ToString();
                                Member.IsSameAddress = (bool)OReader["IsSameAddress"];
                                Member.FamilyMembers = GetFamilyMembers(Member.FamilyId);
                                Members.Add(Member);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Members;

        }
        public List<EntityMembers> GetFamiliesWithMembersSearch(string SearchKey)
        {
            List<EntityMembers> Members = new List<EntityMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "FAMILYMEMBERSEARCH");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Members;
                            }
                            while (OReader.Read())
                            {
                                EntityMembers Member = new EntityMembers();
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.CHChurchid = (Guid)OReader["CHChurchid"];
                                Member.FamilyNo = (Int32)OReader["FamilyNo"];
                                Member.ApplicantName = OReader["ApplicantName"].ToString();
                                Member.FamilyName = OReader["FamilyName"].ToString();
                                Member.TelephoneNo = OReader["TelephoneNo"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Occupation = OReader["Occupation"].ToString();
                                Member.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Member.SinceWhen = OReader["SinceWhen"].ToString();
                                Member.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Member.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Member.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Member.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Member.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Member.SoftCopy = OReader["SoftCopy"].ToString();
                                Member.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Member.PermanentCity = OReader["PermanentCity"].ToString();
                                Member.PermanentCountry = (Guid)OReader["PermanentCountry"];
                                Member.PermanentState = (Guid)OReader["PermanentState"];
                                Member.PermanentPincode = OReader["PermanentPincode"].ToString();
                                Member.PresentAddress = OReader["PresentAddress"].ToString();
                                Member.PresentCity = OReader["PresentCity"].ToString();
                                Member.PresentCountry = (Guid)OReader["PresentCountry"];
                                Member.PresentState = (Guid)OReader["PresentState"];
                                Member.PresentPinCode = OReader["PresentPinCode"].ToString();
                                Member.IsSameAddress = (bool)OReader["IsSameAddress"];
                                Member.FamilyMembers = GetFamilyMembers(Member.FamilyId);
                                Members.Add(Member);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Members;

        }
        public List<EntityMembers> GetMembers()
        {
            List<EntityMembers> Members = new List<EntityMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Family]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Members;
                            }
                            while (OReader.Read())
                            {
                                EntityMembers Member = new EntityMembers();
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.CHChurchid = (Guid)OReader["CHChurchid"];
                                Member.FamilyNo = (Int32)OReader["FamilyNo"];
                                Member.ApplicantName = OReader["ApplicantName"].ToString();
                                Member.FamilyName = OReader["FamilyName"].ToString();
                                Member.TelephoneNo = OReader["TelephoneNo"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Occupation = OReader["Occupation"].ToString();
                                Member.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Member.SinceWhen = OReader["SinceWhen"].ToString();
                                Member.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Member.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Member.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Member.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Member.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Member.SoftCopy = OReader["SoftCopy"].ToString();
                                Member.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Member.PermanentCity = OReader["PermanentCity"].ToString();
                                Member.PermanentCountry = (Guid)OReader["PermanentCountry"];
                                Member.PermanentState = (Guid)OReader["PermanentState"];
                                Member.PermanentPincode = OReader["PermanentPincode"].ToString();
                                Member.PresentAddress = OReader["PresentAddress"].ToString();
                                Member.PresentCity = OReader["PresentCity"].ToString();
                                Member.PresentCountry = (Guid)OReader["PresentCountry"];
                                Member.PresentState = (Guid)OReader["PresentState"];
                                Member.PresentPinCode = OReader["PresentPinCode"].ToString();



                                Member.IsSameAddress = (bool)OReader["IsSameAddress"];
                                Members.Add(Member);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Members;
        }
        public EntityMembers GetMember(Guid FamilyId)
        {
            EntityMembers Member = new EntityMembers();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Family]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Member;
                            }
                            while (OReader.Read())
                            {
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.CHChurchid = (Guid)OReader["CHChurchid"];
                                Member.FamilyNo = (Int32)OReader["FamilyNo"];
                                Member.ApplicantName = OReader["ApplicantName"].ToString();
                                Member.FamilyName = OReader["FamilyName"].ToString();
                                Member.TelephoneNo = OReader["TelephoneNo"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Occupation = OReader["Occupation"].ToString();
                                Member.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Member.SinceWhen = OReader["SinceWhen"].ToString();
                                Member.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Member.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Member.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Member.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Member.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Member.SoftCopy = OReader["SoftCopy"].ToString();
                                Member.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Member.PermanentCity = OReader["PermanentCity"].ToString();
                                Member.PermanentCountry = (Guid)OReader["PermanentCountry"];
                                Member.PermanentState = (Guid)OReader["PermanentState"];
                                Member.PermanentPincode = OReader["PermanentPincode"].ToString();
                                Member.PresentAddress = OReader["PresentAddress"].ToString();
                                Member.PresentCity = OReader["PresentCity"].ToString();
                                Member.PresentCountry = (Guid)OReader["PresentCountry"];
                                Member.PresentState = (Guid)OReader["PresentState"];
                                Member.PresentPinCode = OReader["PresentPinCode"].ToString();
                                
                                
                                
                                Member.IsSameAddress = (bool)OReader["IsSameAddress"];
                                Member.Status = (Int32)OReader["Status"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Member;
        }
        public string DeleteMember(Guid FamilyId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IUD_Family]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public DataTable GetAllFamilyInChurchReport()
        {
            DataTable dt = new DataTable();
            EntityMembers Member = new EntityMembers();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyReport]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ALLFAMILYBYCHURCHID");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        dt = SQLServer.ExecuteDataSet(dbCommand).Tables[0];
                        return dt;
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return dt;
        }
        #endregion
        #region FamilyDetails
        public List<EntityBirthDay> GetBirthDayList(Guid ChurchId)
        {
            List<EntityBirthDay> BirthDays = new List<EntityBirthDay>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_BirthDays]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, BirthDay.TODAY);
                    SQLServer.AddInParameter(dbCommand, "@Usertype", DbType.String, "BELIVER");
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return BirthDays;
                            }
                            while (OReader.Read())
                            {
                                EntityBirthDay Birth = new EntityBirthDay();
                                Birth.Name = OReader["Name"].ToString();
                                Birth.Phone = OReader["Phone"].ToString();
                                Birth.Email = OReader["Email"].ToString();
                                Birth.CurrentAge = OReader["CurrentAge"].ToString();
                                Birth.Date = OReader["Date"].ToString();
                                Birth.DateOfBirth = OReader["DateOfBirth"].ToString();
                                Birth.FatherName = OReader["FamilyName"].ToString();
                                Birth.MemberId = (Guid)OReader["MemberId"];
                                BirthDays.Add(Birth);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return BirthDays;
        }
        public string InsertFamilyMembers(EntityFamilyMembers FamilyMembers)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyMembers.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, FamilyMembers.Name);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, FamilyMembers.Phone);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, FamilyMembers.Email);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, FamilyMembers.Gender);
                    SQLServer.AddInParameter(dbCommand, "@RelationShip", DbType.Guid, FamilyMembers.RelationShipId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, FamilyMembers.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@DateOfDedication", DbType.String, FamilyMembers.DateOfDedication);
                    SQLServer.AddInParameter(dbCommand, "@DateofSalvation", DbType.String, FamilyMembers.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, FamilyMembers.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfHgBaptism", DbType.String, FamilyMembers.DateOfHgBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfMarriage", DbType.String, FamilyMembers.DateOfMarriage);
                    SQLServer.AddInParameter(dbCommand, "@DateOfLeaving", DbType.String, FamilyMembers.DateOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@ReasonOfLeaving", DbType.String, FamilyMembers.ReasonOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, FamilyMembers.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, FamilyMembers.Status);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateFamilyMembers(EntityFamilyMembers FamilyMembers)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@Memberid", DbType.Guid, FamilyMembers.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyMembers.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, FamilyMembers.Name);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, FamilyMembers.Phone);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, FamilyMembers.Email);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, FamilyMembers.Gender);
                    SQLServer.AddInParameter(dbCommand, "@RelationShip", DbType.Guid, FamilyMembers.RelationShipId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, FamilyMembers.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@DateOfDedication", DbType.String, FamilyMembers.DateOfDedication);
                    SQLServer.AddInParameter(dbCommand, "@DateofSalvation", DbType.String, FamilyMembers.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, FamilyMembers.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfHgBaptism", DbType.String, FamilyMembers.DateOfHgBaptism);
                    SQLServer.AddInParameter(dbCommand, "@DateOfMarriage", DbType.String, FamilyMembers.DateOfMarriage);
                    SQLServer.AddInParameter(dbCommand, "@DateOfLeaving", DbType.String, FamilyMembers.DateOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@ReasonOfLeaving", DbType.String, FamilyMembers.ReasonOfLeaving);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, FamilyMembers.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, FamilyMembers.Status);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityFamilyMembers> GetFamilyMembers(Guid FamilyId)
        {
            List<EntityFamilyMembers> FamilyMembers = new List<EntityFamilyMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "FAMILY");
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return FamilyMembers;
                            }
                            while (OReader.Read())
                            {
                                EntityFamilyMembers Member = new EntityFamilyMembers();
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.MemberId = (Guid)OReader["MemberId"];
                                Member.Name = OReader["Name"].ToString();
                                Member.Phone = OReader["Phone"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Gender = (int)OReader["Gender"];
                                Member.RelationShipId = (Guid)OReader["RelationShipId"];
                                Member.DateOfBirth = OReader["DateOfBirth"].ToString();
                                Member.DateOfDedication = OReader["DateOfDedication"].ToString();
                                Member.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                Member.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                Member.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                Member.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                Member.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                Member.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                Member.Remarks = OReader["Remarks"].ToString();
                                Member.MemberNumber = (int)OReader["MemberNumber"];

                                Member.CurrentAge = OReader["CurrentAge"].ToString();
                                Member.IsBaptized = OReader["IsBaptized"].ToString();
                                Member.IsGetHgBaptism = OReader["IsGetHgBaptism"].ToString();
                                Member.IsGetSalvation = OReader["IsGetSalvation"].ToString();
                                Member.IsLeft = OReader["IsLeft"].ToString();
                                Member.IsMarried = OReader["IsMarried"].ToString();
                                Member.IsHavingJobtitle = OReader["IsHavingJobtitle"].ToString();

                                Member.Status = (int)OReader["Status"];
                                Member.RelationShip = new EntityRelationShip();
                                Member.RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                Member.RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                FamilyMembers.Add(Member);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return FamilyMembers;
        }
        public List<EntityFamilyMembers> GetFamilyMembersByChurch(Guid Churchid, string SearchText)
        {
            List<EntityFamilyMembers> FamilyMembers = new List<EntityFamilyMembers>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "SEARCH");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Churchid);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchText);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return FamilyMembers;
                            }
                            while (OReader.Read())
                            {
                                EntityFamilyMembers Member = new EntityFamilyMembers();
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.MemberId = (Guid)OReader["MemberId"];
                                Member.Name = OReader["Name"].ToString();
                                Member.Phone = OReader["Phone"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Gender = (int)OReader["Gender"];
                                Member.RelationShipId = (Guid)OReader["RelationShipId"];
                                Member.DateOfBirth = OReader["DateOfBirth"].ToString();
                                Member.DateOfDedication = OReader["DateOfDedication"].ToString();
                                Member.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                Member.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                Member.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                Member.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                Member.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                Member.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                Member.Remarks = OReader["Remarks"].ToString();                     
                                Member.Status = (int)OReader["Status"];
                                Member.RelationShip = new EntityRelationShip();
                                Member.RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                Member.RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                Member.Members = new EntityMembers();
                                Member.Members.FamilyName = OReader["FamilyName"].ToString();
                                Member.Members.FamilyNo = (int)OReader["FamilyNo"];
                                Member.Members.ApplicantName = OReader["ApplicantName"].ToString();
                                Member.Members.ApplicationDate = OReader["ApplicationDate"].ToString();
                                Member.Members.ApplicationPlace = OReader["ApplicationPlace"].ToString();
                                Member.Members.Email = OReader["Email"].ToString();
                                Member.Members.IsSameAddress = (bool)OReader["IsSameAddress"];
                                Member.Members.Occupation = OReader["Occupation"].ToString();
                                Member.Members.PermanentAddress = OReader["PermanentAddress"].ToString();
                                Member.Members.PermanentCity = OReader["PermanentCity"].ToString();
                                //Member.Members.PermanentCountry = OReader["RelationShipName"].ToString();
                                Member.Members.PermanentPincode = OReader["PermanentPincode"].ToString();
                                //Member.Members.PermanentState = OReader["RelationShipName"].ToString();
                                Member.Members.PlaceAndChurch = OReader["PlaceAndChurch"].ToString();
                                Member.Members.PlaceOfChurch = OReader["PlaceOfChurch"].ToString();
                                Member.Members.PresentAddress = OReader["PresentAddress"].ToString();
                                Member.Members.PresentCity = OReader["PresentCity"].ToString();
                                //Member.Members.PresentCountry = OReader["RelationShipName"].ToString();
                                Member.Members.PresentPinCode = OReader["PresentPinCode"].ToString();
                                //Member.Members.PresentState = OReader["RelationShipName"].ToString();
                                Member.Members.PreviousChurch = OReader["PreviousChurch"].ToString();
                                Member.Members.ReasonforSelecting = OReader["ReasonforSelecting"].ToString();
                                Member.Members.SinceWhen = OReader["SinceWhen"].ToString();
                                Member.Members.SoftCopy = OReader["SoftCopy"].ToString();
                                Member.Members.Status = (int)OReader["Status"];
                                Member.Members.TelephoneNo = OReader["TelephoneNo"].ToString();
                                FamilyMembers.Add(Member);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return FamilyMembers;
        }
        public DataTable GetFamilyDetailsReport(Guid FamilyId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyReport]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "FAMILYDETAILS");
                    SQLServer.AddInParameter(dbCommand, "@Id", DbType.Guid, FamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        dt = SQLServer.ExecuteDataSet(dbCommand).Tables[0];
                        return dt;
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return dt;
        }
        public DataTable GetFamilyMembersReport(Guid ChurchId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyReport]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ALLFAMILYWITHMEMBERS");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        dt = SQLServer.ExecuteDataSet(dbCommand).Tables[0];
                        return dt;
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return dt;
        }
        public EntityFamilyMembers GetFamilyMember(Guid MemberId)
        {
            EntityFamilyMembers Member = new EntityFamilyMembers();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, MemberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Member;
                            }
                            while (OReader.Read())
                            {
                                Member.FamilyId = (Guid)OReader["FamilyId"];
                                Member.MemberId = (Guid)OReader["MemberId"];
                                Member.Name = OReader["Name"].ToString();
                                Member.Phone = OReader["Phone"].ToString();
                                Member.Email = OReader["Email"].ToString();
                                Member.Gender = (int)OReader["Gender"];
                                Member.RelationShipId = (Guid)OReader["RelationShipId"];
                                Member.DateOfBirth = OReader["DateOfBirth"].ToString();
                                Member.DateOfDedication = OReader["DateOfDedication"].ToString();
                                Member.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                Member.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                Member.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString();
                                Member.DateOfLeaving = OReader["DateOfLeaving"].ToString();
                                Member.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                Member.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                Member.Remarks = OReader["Remarks"].ToString();
                                Member.Status = (int)OReader["Status"];
                                Member.RelationShip = new EntityRelationShip();
                                Member.RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                Member.RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Member;
        }
        public string DeleteFamilyMembers(Guid MemberId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_FamilyMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, MemberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntitySelectFamilyDetails> GetFamilyMembersByChurchId(Guid ChurchId)
        {
            List<EntitySelectFamilyDetails> FamilyMembers = new List<EntitySelectFamilyDetails>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_AllfamilyMembersByChurchId]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "FAMILYMEMBERS");
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return FamilyMembers;
                            }
                            while (OReader.Read())
                            {
                                EntitySelectFamilyDetails Members = new EntitySelectFamilyDetails();
                                Members.FamilyId = (Guid)OReader["FamilyId"];
                                Members.MemberId = (Guid)OReader["MemberId"];
                                Members.FamilyName = OReader["FamilyName"].ToString();
                                Members.MemberName = OReader["Name"].ToString();
                                FamilyMembers.Add(Members);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return FamilyMembers;
        }
        public EntityMemberFullDetails GetFamilyMemberFulldetails(Guid MemberId)
        {
            EntityMemberFullDetails Member = new EntityMemberFullDetails();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyMembersfulldetails]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, MemberId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Member;
                            }
                            while (OReader.Read())
                            {
                                //Member.FamilyMembers = new EntityFamilyMembers();
                                //Member.FamilyMembers.FamilyId = (Guid)OReader["FamilyId"];
                                //Member.FamilyMembers.MemberId = (Guid)OReader["MemberId"];
                                //Member.FamilyMembers.Name = OReader["Name"].ToString();
                                //Member.FamilyMembers.Phone = OReader["Phone"].ToString();
                                //Member.FamilyMembers.Email = OReader["Email"].ToString();
                                //Member.FamilyMembers.Gender = (int)OReader["Gender"];
                                //Member.FamilyMembers.RelationShipId = (Guid)OReader["RelationShipId"];
                                //Member.FamilyMembers.DateOfBirth = OReader["DateOfBirth"].ToString());
                                //Member.FamilyMembers.DateOfDedication = OReader["DateOfDedication"].ToString());
                                //Member.FamilyMembers.DateOfBaptism = OReader["DateOfBaptism"].ToString());
                                //Member.FamilyMembers.DateOfSalvation = OReader["DateOfSalvation"].ToString());
                                //Member.FamilyMembers.DateOfHgBaptism = OReader["DateOfHgBaptism"].ToString());
                                //Member.FamilyMembers.DateOfLeaving = OReader["DateOfLeaving"].ToString());
                                //Member.FamilyMembers.DateOfMarriage = OReader["DateOfMarriage"].ToString());
                                //Member.FamilyMembers.ReasonOfLeaving = OReader["ReasonOfLeaving"].ToString();
                                //Member.FamilyMembers.Remarks = OReader["Remarks"].ToString();
                                //Member.FamilyMembers.Status = (int)OReader["Status"];
                                //Member.FamilyMembers.RelationShip = new EntityRelationShip();
                                //Member.FamilyMembers.RelationShip.RelationShipId = (Guid)OReader["RelationShipId"];
                                //Member.FamilyMembers.RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                //Member.Members = new EntityMembers();
                                //Member.Members.FamilyHeadName = OReader["FamilyHeadName"].ToString();
                                //Member.Members.familyNo = (int)OReader["familyNo"];
                                //Member.Members.FamilyHeadName = OReader["FamilyHeadName"].ToString();
                                //Member.Members.CommunicationCountryId = (Guid)OReader["CommunicationCountryId"];
                                //Member.Members.CommunicationStateId = (Guid)OReader["CommunicationStateId"];
                                //Member.Members.CommunicationCity = OReader["CommunicationCity"].ToString();
                                //Member.Members.CommunicationAddress = OReader["CommunicationAddress"].ToString();
                                //Member.Members.CommunicationPinCode = OReader["CommunicationPinCode"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Member;
        }
        #endregion
        #region Baptism
        public string InsertBaptism(EntityBaptism Baptism)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Baptism]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@BaptismId", DbType.Guid, Baptism.BaptismId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, Baptism.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyNo", DbType.Int32, Baptism.FamilyNo);
                    SQLServer.AddInParameter(dbCommand, "@FamilyName", DbType.String, Baptism.FamilyName);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, Baptism.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@DateOfSalvation", DbType.String, Baptism.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, Baptism.Gender);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, Baptism.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, Baptism.StateId);
                    SQLServer.AddInParameter(dbCommand, "@AddressLine1", DbType.String, Baptism.AddressLine1);
                    SQLServer.AddInParameter(dbCommand, "@AddressLine2", DbType.String, Baptism.AddressLine2);
                    SQLServer.AddInParameter(dbCommand, "@Pincode", DbType.String, Baptism.Pincode);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@NameOfChurchworker", DbType.String, Baptism.NameOfChurchworker);
                    SQLServer.AddInParameter(dbCommand, "@AttendingYears", DbType.String, Baptism.AttendingYears);
                    SQLServer.AddInParameter(dbCommand, "@Notes", DbType.String, Baptism.Notes);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, Baptism.Name);
                    SQLServer.AddInParameter(dbCommand, "@NameOfBaptizer", DbType.String, Baptism.NameOfBaptizer);
                    SQLServer.AddInParameter(dbCommand, "@BaptizerId", DbType.Guid, Baptism.BaptizerId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, Baptism.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfBaptism", DbType.String, Baptism.PlaceOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@ChurchName", DbType.String, Baptism.ChurchName);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfApplication", DbType.String, Baptism.PlaceOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfApplication", DbType.String, Baptism.DateOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationImage", DbType.String, Baptism.ApplicationImage);
                    SQLServer.AddInParameter(dbCommand, "@BaptizerName", DbType.String, Baptism.BaptizerName);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Baptism.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Baptism.Email);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, Baptism.Phone);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public string UpdateBaptism(EntityBaptism Baptism)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Baptism]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@BaptismId", DbType.Guid, Baptism.BaptismId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, Baptism.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyNo", DbType.Int32, Baptism.FamilyNo);
                    SQLServer.AddInParameter(dbCommand, "@FamilyName", DbType.String, Baptism.FamilyName);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, Baptism.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@DateOfSalvation", DbType.String, Baptism.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, Baptism.Gender);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, Baptism.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, Baptism.StateId);
                    SQLServer.AddInParameter(dbCommand, "@AddressLine1", DbType.String, Baptism.AddressLine1);
                    SQLServer.AddInParameter(dbCommand, "@AddressLine2", DbType.String, Baptism.AddressLine2);
                    SQLServer.AddInParameter(dbCommand, "@Pincode", DbType.String, Baptism.Pincode);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@NameOfChurchworker", DbType.String, Baptism.NameOfChurchworker);
                    SQLServer.AddInParameter(dbCommand, "@AttendingYears", DbType.String, Baptism.AttendingYears);
                    SQLServer.AddInParameter(dbCommand, "@Notes", DbType.String, Baptism.Notes);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, Baptism.Name);
                    SQLServer.AddInParameter(dbCommand, "@NameOfBaptizer", DbType.String, Baptism.NameOfBaptizer);
                    //SQLServer.AddInParameter(dbCommand, "@BaptizerId", DbType.Guid, Baptism.BaptizerId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, Baptism.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfBaptism", DbType.String, Baptism.PlaceOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@ChurchName", DbType.String, Baptism.ChurchName);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfApplication", DbType.String, Baptism.PlaceOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfApplication", DbType.String, Baptism.DateOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@ApplicationImage", DbType.String, Baptism.ApplicationImage);
                    SQLServer.AddInParameter(dbCommand, "@BaptizerName", DbType.String, Baptism.BaptizerName);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Baptism.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Baptism.Email);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, Baptism.Phone);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public string DeleteBaptism(Guid BaptismId)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Baptism]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@BaptismId", DbType.Guid, BaptismId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public List<EntityBaptism> GetBaptism()
        {
            List<EntityBaptism> Baptisms = new List<EntityBaptism>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Baptism]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader oReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (null == oReader)
                            {
                                return Baptisms;
                            }

                            while (oReader.Read())
                            {
                                EntityBaptism Baptism = new EntityBaptism();
                                Baptism.BaptismId = (Guid)oReader["BaptismId"];
                                Baptism.FamilyId = (Guid)oReader["FamilyId"];
                                Baptism.FamilyNo = (Int32)oReader["FamilyNo"];
                                Baptism.FamilyName = oReader["FamilyName"].ToString();
                                Baptism.DateOfBirth = oReader["DateOfBirth"].ToString();
                                Baptism.DateOfSalvation = oReader["DateOfSalvation"].ToString();
                                Baptism.Gender = (Int32)oReader["Gender"];
                                Baptism.CountryId = (Guid)oReader["CountryId"];
                                Baptism.StateId = (Guid)oReader["StateId"];
                                Baptism.AddressLine1 = oReader["AddressLine1"].ToString();
                                Baptism.AddressLine2 = oReader["AddressLine2"].ToString();
                                Baptism.Pincode = oReader["Pincode"].ToString();
                                Baptism.NameOfChurchworker = oReader["NameOfChurchworker"].ToString();
                                Baptism.AttendingYears = oReader["AttendingYears"].ToString();
                                Baptism.Notes = oReader["Notes"].ToString();
                                Baptism.Name = oReader["Name"].ToString();
                                Baptism.NameOfBaptizer = oReader["NameOfBaptizer"].ToString();
                                //Baptism.BaptizerId = (Guid)oReader["BaptizerId"];
                                Baptism.DateOfBaptism = oReader["DateOfBaptism"].ToString();
                                Baptism.PlaceOfBaptism = oReader["PlaceOfBaptism"].ToString();
                                Baptism.ChurchName = oReader["ChurchName"].ToString();
                                Baptism.PlaceOfApplication = oReader["PlaceOfApplication"].ToString();
                                Baptism.DateOfApplication = oReader["DateOfApplication"].ToString();
                                Baptism.ApplicationImage = oReader["ApplicationImage"].ToString();
                                Baptism.BaptizerName = oReader["BaptizerName"].ToString();
                                Baptism.MemberId = (Guid)oReader["MemberId"];
                                Baptism.Email = oReader["Email"].ToString();
                                Baptism.Phone = oReader["Phone"].ToString();
                                Baptism.status = (Int32)oReader["Gender"];
                                Baptisms.Add(Baptism);
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
            }
            return Baptisms;
        }
        public EntityBaptism GetBaptism(Guid BaptismId)
        {
            EntityBaptism Baptism = new EntityBaptism();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Baptism]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@BaptismId", DbType.Guid, BaptismId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader oReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (null == oReader)
                            {
                                return Baptism;
                            }

                            while (oReader.Read())
                            {
                                Baptism.BaptismId = (Guid)oReader["BaptismId"];
                                Baptism.FamilyId = (Guid)oReader["FamilyId"];
                                Baptism.FamilyNo = (Int32)oReader["FamilyNo"];
                                Baptism.FamilyName = oReader["FamilyName"].ToString();
                                Baptism.DateOfBirth = oReader["DateOfBirth"].ToString();
                                Baptism.DateOfSalvation = oReader["DateOfSalvation"].ToString();
                                Baptism.Gender = (Int32)oReader["Gender"];
                                Baptism.CountryId = (Guid)oReader["CountryIdb
                                Baptism.StateId = (Guid)oReader["StateId"];
                                Baptism.AddressLine1 = oReader["AddressLine1"].ToString();
                                Baptism.AddressLine2 = oReader["AddressLine2"].ToString();
                                Baptism.Pincode = oReader["Pincode"].ToString();
                                Baptism.NameOfChurchworker = oReader["NameOfChurchworker"].ToString();
                                Baptism.AttendingYears = oReader["AttendingYears"].ToString();
                                Baptism.Notes = oReader["Notes"].ToString();
                                Baptism.Name = oReader["Name"].ToString();
                                Baptism.NameOfBaptizer = oReader["NameOfBaptizer"].ToString();
                                //Baptism.BaptizerId = (Guid)oReader["BaptizerId"];
                                Baptism.DateOfBaptism = oReader["DateOfBaptism"].ToString();
                                Baptism.PlaceOfBaptism = oReader["PlaceOfBaptism"].ToString();
                                Baptism.ChurchName = oReader["ChurchName"].ToString();
                                Baptism.PlaceOfApplication = oReader["PlaceOfApplication"].ToString();
                                Baptism.DateOfApplication = oReader["DateOfApplication"].ToString();
                                Baptism.ApplicationImage = oReader["ApplicationImage"].ToString();
                                Baptism.BaptizerName = oReader["BaptizerName"].ToString();
                                Baptism.MemberId = (Guid)oReader["MemberId"];
                                Baptism.Email = oReader["Email"].ToString();
                                Baptism.Phone = oReader["Phone"].ToString();
                                Baptism.status = (Int32)oReader["Gender"];
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
            }
            return Baptism;
        }
        public DataTable GetBaptismDetailsforPrint(Guid BaptismId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_FamilyReport]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "BAPTISMREPORT");
                    SQLServer.AddInParameter(dbCommand, "@BaptismId", DbType.Guid, BaptismId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        dt = SQLServer.ExecuteDataSet(dbCommand).Tables[0];
                        return dt;
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return dt;
        }
        #endregion
        #region Membershipfee
        public string InsertMembershipfee(EntityMembershipfee Membershipfee)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Membershipfee]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Membershipfee.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@Familyid", DbType.Guid, Membershipfee.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@DateofPay", DbType.String, Membershipfee.DateofPayment);
                    SQLServer.AddInParameter(dbCommand, "@Amount", DbType.Double, Membershipfee.Amount);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@payMode", DbType.String, Membershipfee.PaymentMode);
                    SQLServer.AddInParameter(dbCommand, "@year", DbType.String, Membershipfee.Year);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public string UpdateBaptismMembershipfee(EntityMembershipfee Membershipfee)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Membershipfee]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, Membershipfee.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@Familyid", DbType.Guid, Membershipfee.FamilyId);
                    SQLServer.AddInParameter(dbCommand, "@DateofPay", DbType.String, Membershipfee.DateofPayment);
                    SQLServer.AddInParameter(dbCommand, "@Amount", DbType.Double, Membershipfee.Amount);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@payMode", DbType.String, Membershipfee.PaymentMode);
                    SQLServer.AddInParameter(dbCommand, "@year", DbType.String, Membershipfee.Year);
                    SQLServer.AddInParameter(dbCommand, "@FeeId", DbType.Guid, Membershipfee.FeeId);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public string DeleteMembershipfee(Guid FeeId)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Membershipfee]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@FeeId", DbType.Guid, FeeId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public List<EntityMembershipfee> GetMembershipfee(string year, Guid ChurchId)
        {
            List<EntityMembershipfee> Membershipfees = new List<EntityMembershipfee>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Membershipfee]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@Year", DbType.String, year);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader oReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (null == oReader)
                            {
                                return Membershipfees;
                            }

                            while (oReader.Read())
                            {
                                EntityMembershipfee Membershipfee = new EntityMembershipfee();
                                //Membershipfee.FeeId = (Guid)oReader["FeeId"];
                                //Membershipfee.FamilyId = (Guid)oReader["FamilyId"];
                                //Membershipfee.MemberId = (Guid)oReader["MemberId"];
                                //Membershipfee.DateofPayment = oReader["DateofPayment"].ToString();
                                //Membershipfee.ChurchId = (Guid)oReader["ChurchId"];
                                //Membershipfee.Amount = (double)oReader["Amount"];
                                //Membershipfee.PaymentMode = oReader["PaymentMode"].ToString();
                                //Membershipfee.Year = oReader["Year"].ToString();
                                //Membershipfee.Members = new EntityMembers();
                                //Membershipfee.Members.familyNo = (Int32)oReader["familyNo"];
                                //Membershipfee.Members.FamilyId = (Guid)oReader["FamilyId"];
                                //Membershipfee.Members.FamilyHeadName = oReader["FamilyHeadName"].ToString();
                                //Membershipfee.FamilyMembers = new EntityFamilyMembers();
                                //Membershipfee.FamilyMembers.Name = oReader["Name"].ToString();
                                //Membershipfee.FamilyMembers.MemberId = (Guid)oReader["MemberId"];
                                //Membershipfees.Add(Membershipfee);
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
            }
            return Membershipfees;
        }
        public EntityMembershipfee GetMembershipfee(Guid FeeId)
        {
            EntityMembershipfee Membershipfee = new EntityMembershipfee();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Membershipfee]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@FeeId", DbType.Guid, FeeId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader oReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (null == oReader)
                            {
                                return Membershipfee;
                            }

                            while (oReader.Read())
                            {
                                //Membershipfee.FeeId = (Guid)oReader["FeeId"];
                                //Membershipfee.FamilyId = (Guid)oReader["FamilyId"];
                                //Membershipfee.MemberId = (Guid)oReader["MemberId"];
                                //Membershipfee.DateofPayment = oReader["DateofPayment"].ToString());
                                //Membershipfee.ChurchId = (Guid)oReader["ChurchId"];
                                //Membershipfee.Amount = (double)oReader["Amount"];
                                //Membershipfee.PaymentMode = oReader["PaymentMode"].ToString();
                                //Membershipfee.Year = oReader["Year"].ToString();
                                //Membershipfee.Members = new EntityMembers();
                                //Membershipfee.Members.familyNo = (Int32)oReader["familyNo"];
                                //Membershipfee.Members.FamilyHeadName = oReader["FamilyHeadName"].ToString();
                                //Membershipfee.FamilyMembers = new EntityFamilyMembers();
                                //Membershipfee.FamilyMembers.Name = oReader["Name"].ToString();
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
            }
            return Membershipfee;
        }
        #endregion
        #region AssignJobtitle
        public string InsertMemberRoles(EntityMemberRoles MemberRoles)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_AssignJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@validfrom", DbType.String, MemberRoles.validfrom);
                    SQLServer.AddInParameter(dbCommand, "@validto", DbType.String, MemberRoles.validto);
                    SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, MemberRoles.CreateLogin);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, MemberRoles.Status);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, MemberRoles.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, MemberRoles.BoardJobtitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public string UpdateMemberRoles(EntityMemberRoles MemberRoles)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_AssignJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@validfrom", DbType.String, MemberRoles.validfrom);
                    SQLServer.AddInParameter(dbCommand, "@validto", DbType.String, MemberRoles.validto);
                    SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, MemberRoles.CreateLogin);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, MemberRoles.Status);
                    SQLServer.AddInParameter(dbCommand, "@MemberId", DbType.Guid, MemberRoles.MemberId);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, MemberRoles.BoardJobtitleId);
                    SQLServer.AddInParameter(dbCommand, "@RoleId", DbType.Guid, MemberRoles.RoleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public string DeleteMemberRoles(Guid RoleId)
        {
            string iReturn = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_AssignJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@RoleId", DbType.Guid, RoleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        iReturn = (string)SQLServer.ExecuteScalar(dbCommand);
                    }

                }
            }
            catch (Exception ex)
            {
            }
            return iReturn;
        }
        public List<EntityMemberRoles> GetMemberRoles(Guid ChurchId)
        {
            List<EntityMemberRoles> MemberRoles = new List<EntityMemberRoles>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_JobtitleRoleMembers]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader oReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (null == oReader)
                            {
                                return MemberRoles;
                            }

                            while (oReader.Read())
                            {
                                EntityMemberRoles MemberRole = new EntityMemberRoles();
                                MemberRole.RoleId = (Guid)oReader["RoleId"];
                                MemberRole.ChurchId = (Guid)oReader["ChurchId"];
                                MemberRole.MemberId = (Guid)oReader["MemberId"];
                                MemberRole.BoardJobtitleId = (Guid)oReader["BoardJobtitleId"];
                                MemberRole.validfrom = oReader["validfrom"].ToString();
                                MemberRole.validto = oReader["validto"].ToString();
                                MemberRole.CreateLogin = (bool)oReader["CreateLogin"];
                                MemberRole.ChurchBoardJobtitle = new EntityChurchBoardJobtitle();
                                MemberRole.ChurchBoardJobtitle.BoardId = (Guid)oReader["BoardId"];
                                MemberRole.ChurchBoardJobtitle.JobtitleName = oReader["JobtitleName"].ToString();
                                MemberRole.FamilyMembers = new EntityFamilyMembers();
                                MemberRole.FamilyMembers.Name = "";
                                MemberRoles.Add(MemberRole);
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
            }
            return MemberRoles;
        }
        #endregion
        #region ChildDedication
        public string InsertChildDedication(EntityChildDedications ChildDedication)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChildDedication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChildDedicationId", DbType.Guid, ChildDedication.ChildDedicationId);
                    SQLServer.AddInParameter(dbCommand, "@CHChurchid", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, ChildDedication.FamilyId);
                    //SQLServer.AddInParameter(dbCommand, "@FatherId", DbType.Guid, ChildDedication.FatherId);
                    //SQLServer.AddInParameter(dbCommand, "@MotherId", DbType.Guid, ChildDedication.MotherId);
                    SQLServer.AddInParameter(dbCommand, "@ChildName", DbType.String, ChildDedication.ChildName);
                    SQLServer.AddInParameter(dbCommand, "@GuardianDetails", DbType.String, ChildDedication.GuardianDetails);
                    //SQLServer.AddInParameter(dbCommand, "@PresentCountryId", DbType.Guid, ChildDedication.CountryId);
                    //SQLServer.AddInParameter(dbCommand, "@PresentStateId", DbType.Guid, ChildDedication.StateId);
                    //SQLServer.AddInParameter(dbCommand, "@PresentCity", DbType.String, ChildDedication.PresentCity);
                    //SQLServer.AddInParameter(dbCommand, "@PresentAddress", DbType.String, ChildDedication.PresentAdress);
                    //SQLServer.AddInParameter(dbCommand, "@Pincode", DbType.String, ChildDedication.PresentPin);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, ChildDedication.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChildDedication.Gender);
                    //SQLServer.AddInParameter(dbCommand, "@YearOfMembership", DbType.String, ChildDedication.YearOfMemberShip);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfApplication", DbType.String, ChildDedication.PlaceOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfApplication", DbType.String, ChildDedication.DateOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfRecivingApplication", DbType.String, ChildDedication.DateOfRecivingApplication);
                    SQLServer.AddInParameter(dbCommand, "@Reason", DbType.String, ChildDedication.Reason);
                    SQLServer.AddInParameter(dbCommand, "@DateOfChildDedication", DbType.String, ChildDedication.DateOfChildDedication);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfChildDedication", DbType.String, ChildDedication.PlaceOfChildDedication);
                    //SQLServer.AddInParameter(dbCommand, "@Occation", DbType.String, ChildDedication.Occation);
                    SQLServer.AddInParameter(dbCommand, "@ChildDedicatedBy", DbType.String, ChildDedication.ChildDedicatedBy);
                    SQLServer.AddInParameter(dbCommand, "@PhotoCopyOfBirthCertificate", DbType.String, ChildDedication.PhotoCopyOfBirthCertificate);
                    SQLServer.AddInParameter(dbCommand, "@PhotoCopyOfApplication", DbType.String, ChildDedication.PhotoCopyOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@Image", DbType.String, ChildDedication.ImageOfChild);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChildDedication(EntityChildDedications ChildDedication)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChildDedication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChildDedicationId", DbType.Guid, ChildDedication.ChildDedicationId);
                    SQLServer.AddInParameter(dbCommand, "@CHChurchid", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@FamilyId", DbType.Guid, ChildDedication.FamilyId);
                    //SQLServer.AddInParameter(dbCommand, "@FatherId", DbType.Guid, ChildDedication.FatherId);
                    //SQLServer.AddInParameter(dbCommand, "@MotherId", DbType.Guid, ChildDedication.MotherId);
                    SQLServer.AddInParameter(dbCommand, "@ChildName", DbType.String, ChildDedication.ChildName);
                    SQLServer.AddInParameter(dbCommand, "@GuardianDetails", DbType.String, ChildDedication.GuardianDetails);
                    //SQLServer.AddInParameter(dbCommand, "@PresentCountryId", DbType.Guid, ChildDedication.CountryId);
                    //SQLServer.AddInParameter(dbCommand, "@PresentStateId", DbType.Guid, ChildDedication.StateId);
                    //SQLServer.AddInParameter(dbCommand, "@PresentCity", DbType.String, ChildDedication.PresentCity);
                    //SQLServer.AddInParameter(dbCommand, "@PresentAddress", DbType.String, ChildDedication.PresentAdress);
                    //SQLServer.AddInParameter(dbCommand, "@Pincode", DbType.String, ChildDedication.PresentPin);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, ChildDedication.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChildDedication.Gender);
                    //SQLServer.AddInParameter(dbCommand, "@YearOfMembership", DbType.String, ChildDedication.YearOfMemberShip);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfApplication", DbType.String, ChildDedication.PlaceOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfApplication", DbType.String, ChildDedication.DateOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfRecivingApplication", DbType.String, ChildDedication.DateOfRecivingApplication);
                    SQLServer.AddInParameter(dbCommand, "@Reason", DbType.String, ChildDedication.Reason);
                    SQLServer.AddInParameter(dbCommand, "@DateOfChildDedication", DbType.String, ChildDedication.DateOfChildDedication);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfChildDedication", DbType.String, ChildDedication.PlaceOfChildDedication);
                    //SQLServer.AddInParameter(dbCommand, "@Occation", DbType.String, ChildDedication.Occation);
                    SQLServer.AddInParameter(dbCommand, "@ChildDedicatedBy", DbType.String, ChildDedication.ChildDedicatedBy);
                    SQLServer.AddInParameter(dbCommand, "@PhotoCopyOfBirthCertificate", DbType.String, ChildDedication.PhotoCopyOfBirthCertificate);
                    SQLServer.AddInParameter(dbCommand, "@PhotoCopyOfApplication", DbType.String, ChildDedication.PhotoCopyOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@Image", DbType.String, ChildDedication.ImageOfChild);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChildDedication(Guid ChildDedicationId)

        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChildDedication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChildDedicationId", DbType.Guid, ChildDedicationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChildDedications> GetChildDedications()
        {
            List<EntityChildDedications> ChildDedications = new List<EntityChildDedications>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChildDedication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChildDedications;
                            }
                            while (OReader.Read())
                            {
                                EntityChildDedications ChildDedication = new EntityChildDedications();
                                ChildDedication.ChildDedicationId = (Guid)OReader["ChildDedicationId"];
                                ChildDedication.CHChurchid = (Guid)OReader["CHChurchid"];
                                ChildDedication.FamilyId = (Guid)OReader["FamilyId"];
                                //ChildDedication.FatherId = (Guid)OReader["FatherId"];
                                //ChildDedication.MotherId = (Guid)OReader["MotherId"];
                                ChildDedication.ChildName = OReader["ChildName"].ToString();
                                ChildDedication.GuardianDetails = OReader["GuardianDetails"].ToString();
                                //ChildDedication.CountryId = (Guid)OReader["CountryId"];
                                //ChildDedication.StateId = (Guid)OReader["StateId"];
                                //ChildDedication.PresentCity = OReader["PresentCity"].ToString();
                                //ChildDedication.PresentAdress = OReader["PresentAdress"].ToString();
                                //ChildDedication.PresentPin = OReader["PresentPin"].ToString();
                                ChildDedication.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChildDedication.Gender = (int)OReader["Gender"];
                                //ChildDedication.YearOfMemberShip = OReader["YearOfMemberShip"].ToString();
                                ChildDedication.PlaceOfApplication = OReader["PlaceOfApplication"].ToString();
                                ChildDedication.DateOfApplication = OReader["DateOfApplication"].ToString();
                                ChildDedication.DateOfRecivingApplication = OReader["DateOfRecivingApplication"].ToString();
                                ChildDedication.Reason = OReader["Reason"].ToString();
                                ChildDedication.DateOfChildDedication = OReader["DateOfChildDedication"].ToString();
                                ChildDedication.PlaceOfChildDedication = OReader["PlaceOfChildDedication"].ToString();
                                //ChildDedication.Occation = OReader["Occation"].ToString();
                                ChildDedication.ChildDedicatedBy = OReader["ChildDedicatedBy"].ToString();
                                ChildDedication.PhotoCopyOfBirthCertificate = OReader["PhotoCopyOfBirthCertificate"].ToString();
                                ChildDedication.PhotoCopyOfApplication = OReader["PhotoCopyOfApplication"].ToString();
                                ChildDedication.ApplicationStatus = (int)OReader["ApplicationStatus"];
                                ChildDedication.Family = GetMember(ChildDedication.FamilyId);
                                //ChildDedication.FamilyMembers = GetFamilyMembers(ChildDedication.FamilyId);
                                ChildDedications.Add(ChildDedication);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChildDedications;
        }
        public EntityChildDedications GetChildDedication(Guid ChildDedicationId)
        {
            EntityChildDedications ChildDedication = new EntityChildDedications();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChildDedication]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    SQLServer.AddInParameter(dbCommand, "@ChildDedicationId", DbType.Guid, ChildDedicationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChildDedication;
                            }
                            while (OReader.Read())
                            {
                                ChildDedication.ChildDedicationId = (Guid)OReader["ChildDedicationId"];
                                ChildDedication.CHChurchid = (Guid)OReader["CHChurchid"];
                                ChildDedication.FamilyId = (Guid)OReader["FamilyId"];
                                //ChildDedication.FatherId = (Guid)OReader["FatherId"];
                                //ChildDedication.MotherId = (Guid)OReader["MotherId"];
                                ChildDedication.ChildName = OReader["ChildName"].ToString();
                                ChildDedication.GuardianDetails = OReader["GuardianDetails"].ToString();
                                //ChildDedication.CountryId = (Guid)OReader["CountryId"];
                                //ChildDedication.StateId = (Guid)OReader["StateId"];
                                //ChildDedication.PresentCity = OReader["PresentCity"].ToString();
                                //ChildDedication.PresentAdress = OReader["PresentAdress"].ToString();
                                //ChildDedication.PresentPin = OReader["PresentPin"].ToString();
                                ChildDedication.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChildDedication.Gender = (int)OReader["Gender"];
                                //ChildDedication.YearOfMemberShip = OReader["YearOfMemberShip"].ToString();
                                ChildDedication.PlaceOfApplication = OReader["PlaceOfApplication"].ToString();
                                ChildDedication.DateOfApplication = OReader["DateOfApplication"].ToString();
                                ChildDedication.DateOfRecivingApplication = OReader["DateOfRecivingApplication"].ToString();
                                ChildDedication.Reason = OReader["Reason"].ToString();
                                ChildDedication.DateOfChildDedication = OReader["DateOfChildDedication"].ToString();
                                ChildDedication.PlaceOfChildDedication = OReader["PlaceOfChildDedication"].ToString();
                                //ChildDedication.Occation = OReader["Occation"].ToString();
                                ChildDedication.ChildDedicatedBy = OReader["ChildDedicatedBy"].ToString();
                                ChildDedication.PhotoCopyOfBirthCertificate = OReader["PhotoCopyOfBirthCertificate"].ToString();
                                ChildDedication.PhotoCopyOfApplication = OReader["PhotoCopyOfApplication"].ToString();
                                ChildDedication.ApplicationStatus = (int)OReader["ApplicationStatus"];
                                ChildDedication.Family = GetMember(ChildDedication.FamilyId);
                                //ChildDedication.FamilyMembers = GetFamilyMembers(ChildDedication.FamilyId);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChildDedication;
        }
        #endregion*/
    }
}
